{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 金、银分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当前数据集包括期货主力合约数据，上海现货数据。当前策略主要是金银比在历史极值附近的回归策略。--20181123"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 需要引入的库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#-*-coding:utf-8 -*-\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from datetime import datetime, timedelta\n",
    "import matplotlib.pyplot as plt\n",
    "from pyecharts import Line, Overlap, Grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据所在文件夹\n",
    "future代表期货数据，\n",
    "spot代表现货数据，\n",
    "sh代表上海市场，\n",
    "london代表伦敦市场。数据维护由其他程序承担，本分析程序仅进行分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_future = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\gold.csv\"\n",
    "silver_future = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\silver.csv\"\n",
    "gold_spot = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_spot\\\\gold.csv\"\n",
    "silver_spot = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_spot\\\\silver.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_future_data = pd.read_csv(gold_future,parse_dates=[\"date\"], index_col=[\"date\"])\n",
    "print(\"gold future data before duplicated:%d\" % len(gold_future_data))\n",
    "gold_future_data = gold_future_data[~gold_future_data.reset_index().duplicated().values]\n",
    "print(\"gold future data after duplicated:%d\" % len(gold_future_data))\n",
    "silver_future_data = pd.read_csv(silver_future, parse_dates=[\"date\"],index_col=[\"date\"])\n",
    "silver_future_data = silver_future_data[~silver_future_data.reset_index().duplicated().values]\n",
    "\n",
    "gold_spot_data = pd.read_csv(gold_spot, index_col=[\"date\"])\n",
    "gold_spot_data.index = pd.to_datetime(gold_spot_data.index)\n",
    "silver_spot_data = pd.read_csv(silver_spot, index_col=[\"date\"])\n",
    "silver_spot_data.index = pd.to_datetime(silver_spot_data.index)\n",
    "\n",
    "future_data = pd.merge(gold_future_data,silver_future_data,left_index=True,right_index=True,suffixes=('_gold','_silver'))\n",
    "spot_data = pd.merge(gold_spot_data, silver_spot_data, left_index=True, right_index=True)\n",
    "#spot_data[\"price_gold\"] = pd.to_numeric(spot_data[\"price_gold\"],downcast='float')\n",
    "#spot_data[\"price_silver\"] = pd.to_numeric(spot_data[\"price_silver\"],downcast='float')\n",
    "\n",
    "print(type(gold_spot_data.index))\n",
    "print(type(silver_spot_data.index))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spot_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spot_data[\"price_silver\"]=pd.to_numeric(spot_data[\"price_silver\"])\n",
    "print(type(spot_data.iloc[0,0]))\n",
    "print(type(spot_data.iloc[0,1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#del nan value\n",
    "future_data = future_data.dropna()\n",
    "spot_data = spot_data.dropna()\n",
    "#print(data.head(5))\n",
    "\n",
    "future_data[\"compare\"] = future_data[\"close_gold\"]/future_data[\"close_silver\"]*1000\n",
    "spot_data[\"compare\"] = spot_data[\"price_gold\"]/spot_data[\"price_silver\"]*1000\n",
    "spot_data[\"price_silver\"] = spot_data[\"price_silver\"]/10 #将银价按100g为单位计价，主要是与黄金价格在同一范围\n",
    "spot_data = spot_data.round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_gold = np.array(future_data.loc[:, [\"close_gold\"]])\n",
    "future_data_silver = np.array(future_data.loc[:,[\"close_silver\"]])\n",
    "future_data_compare = np.array(future_data.loc[: ,[\"compare\"]])\n",
    "future_datetime = np.array(future_data.index.map(str))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------------分界线--------------------\n",
    "以下部分为图线，以上部分为数据准备"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1、黄金期货主力合约图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "line_future_gold = Line(\"future gold\")\n",
    "line_future_gold.add(\"future gold\",future_datetime,future_data_gold.round(1),\\\n",
    "                     mark_point=[\"average\",\"max\",\"min\"],\\\n",
    "                     is_datazoom_show=True,\\\n",
    "                    mark_point_symbol=None,\\\n",
    "                    yaxis_min=150,yaxis_max=300)\n",
    "line_future_gold"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2、白银期货主力合约图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "line_future_silver = Line(\"future silver\")\n",
    "line_future_silver.add(\"future silver\",future_datetime,future_data_silver,mark_point=[\"average\",\"max\",\"min\"],is_datazoom_show=True)\n",
    "line_future_silver"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3、期货主力合约金银比及对比数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "line_future = Line(\"future AU/AG\")\n",
    "line_future.add(\"future gold\",future_datetime,future_data_gold.round(1),\\\n",
    "                     mark_point=[\"average\",\"max\",\"min\"]\n",
    "                     )\n",
    "line_future.add(\"future silver\",future_datetime,future_data_silver,mark_point=[\"average\",\"max\",\"min\"],is_datazoom_show=True,\\\n",
    "               datazoom_xaxis_index=[0,1],legend_top=\"50%\")\n",
    "\n",
    "\n",
    "line_future_compare = Line(\"future compare\",title_top=\"50%\")\n",
    "line_future_compare.add(\"future AU/AG\",future_datetime,future_data_compare.round(1),mark_point=[\"average\",\"max\",\"min\"],is_datazoom_show=True,\n",
    "                       yaxis_min=40,yaxis_max=85)\n",
    "\n",
    "grid_future = Grid()\n",
    "grid_future.add(line_future,grid_top=\"60%\")\n",
    "grid_future.add(line_future_compare,grid_bottom=\"60%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4、期货主力合约金银比统计数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data.loc[: ,[\"compare\"]].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "future_data[[\"close_gold\",\"close_silver\",\"compare\"]].sort_values(by=['compare'],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "前一次金银比回归分析。前一次金银比极值出现在2016-02-29\tgold：259.40\tsilver：3312.0\tAU/AG：78.321256"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_2016 = future_data.loc[\"2016-02-29\":,[\"close_gold\",\"close_silver\",\"compare\"]]\n",
    "'''\n",
    "取出从2016-2-29开始的后，第一个小于compare<70的索引\n",
    "future_data_2016[future_data_2016.compare<70].index\n",
    "'''\n",
    "end = future_data_2016[future_data_2016.compare<70].index[0]\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_2016 = future_data.loc[\"2016-02-29\":end, [\"close_gold\",\"close_silver\",\"compare\"]]\n",
    "future_data_2016"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在该时间段如果按照极值78回落到70区间，采用做空gold，做多相同价值合约的silver，区间获利计算如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_open = future_data.loc[\"2016-02-29\",[\"close_gold\"]]#gold 开仓价\n",
    "gold_close = future_data.loc[end,[\"close_gold\"]]#silver 结算价\n",
    "\n",
    "silver_open = future_data.loc[\"2016-02-29\",[\"close_silver\"]]\n",
    "silver_close = future_data.loc[end,[\"close_silver\"]]\n",
    "\n",
    "gold_total_value = gold_open.iloc[0] * 1000\n",
    "silver_amount = round(gold_total_value /(silver_open.iloc[0] * 15))\n",
    "silver_total_value = silver_open.iloc[0] * 15 * silver_amount\n",
    "\n",
    "gold_return =  gold_total_value - gold_close.iloc[0] * 1000\n",
    "silver_return =  silver_close.iloc[0] * 15 * silver_amount - silver_total_value\n",
    "fact_return = silver_return - ( -gold_return)\n",
    "\n",
    "print(\"===============================\")\n",
    "print(\"gold 开仓价格: %r, 平仓价格:%r\"%(gold_open.iloc[0],gold_close.iloc[0]))\n",
    "print(\"gold(short)total profit: %r\"%gold_return)\n",
    "print(\"silver 开仓价格: %r, 平仓价格:%r\"%(silver_open.iloc[0],silver_close.iloc[0],))\n",
    "print(\"silver(long) total profit: %r\"%silver_return)\n",
    "print(\"total return: %r\"%fact_return)\n",
    "print(\"total contract value:gold(%r),silver(%r)\"%(gold_total_value.round(2),silver_total_value))\n",
    "print(\"time consume: 2016-02-29-%r\"%(end))\n",
    "print(\"===============================\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "期货主力合约，金银比回归策略，期间最大回撤"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_2016[\"tmp_loss\"] =  (gold_open.iloc[0] - future_data_2016[\"close_gold\"]) * 1000 + \\\n",
    "                               (future_data_2016[\"close_silver\"] - silver_open.iloc[0]) * 15 * silver_amount\n",
    "\n",
    "loss_datetime = np.array(future_data_2016.index.map(str))\n",
    "tmp_loss_value = np.array(future_data_2016.loc[:,[\"tmp_loss\"]])\n",
    "line_loss = Line(\"loss\")\n",
    "line_loss.add(\"loss\",loss_datetime,tmp_loss_value.round(2),mark_point=[\"average\",\"max\",\"min\"],is_stack=True)\n",
    "line_loss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_20 = future_data.iloc[(len(future_data)-20):len(future_data)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_20.loc[:,[\"compare\"]].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "v_5 = future_data_20.loc[:,[\"compare\"]].quantile(0.05)\n",
    "future_data_20.loc[\"5%\"] = v_5\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(future_data)\n",
    "analyse_data = future_data.loc[\"2018/11/23\":\"2019/1/21\",[\"compare\"]]\n",
    "analyse_data.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 现货金银比分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spot_data_gold = np.array(spot_data.loc[:,[\"price_gold\"]])\n",
    "spot_data_silver = np.array(spot_data.loc[:, [\"price_silver\"]])\n",
    "spot_data_compare = np.array(spot_data.loc[:, [\"compare\"]])\n",
    "spot_datatime = np.array(spot_data.index.map(str))\n",
    "\n",
    "line_spot = Line(\"gold_sliver\",title_top=\"50%\")\n",
    "line_spot_compare = Line(\"AU/AG\")\n",
    "\n",
    "line_spot.add(\"gold\", spot_datatime, spot_data_gold, mark_point=[\"average\",\"max\",\"min\"],legend_top=\"50%\",is_stack=True,is_datazoom_show=True,\\\n",
    "               datazoom_xaxis_index=[0,1])\n",
    "line_spot.add(\"silver\",spot_datatime,spot_data_silver,mark_point=[\"average\",\"max\",\"min\"],legend_top=\"50%\",is_stack=True)\n",
    "line_spot_compare.add(\"AU/AG\",spot_datatime,spot_data_compare,mark_point=[\"average\",\"max\",\"min\"],is_datazoom_show=True)\n",
    "\n",
    "grid = Grid()\n",
    "grid.add(line_spot,grid_top=\"60%\")\n",
    "#grid.add(line_silver,grid_top=300)\n",
    "grid.add(line_spot_compare,grid_bottom=\"60%\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "现货金银比的统计规律"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spot_data[\"compare\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "spot_data[[\"price_gold\",\"price_silver\",\"compare\"]].sort_values(by=['compare'],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "期货主力合约与现货，2016-3月段，金银比比较"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_201603 = future_data.loc[\"2016/2/15\":\"2016-12-31\",[\"close_gold\",\"close_silver\",\"compare\"]]\n",
    "spot_data_201603 = spot_data.loc[\"2016-02-15\":\"2016-12-31\",[\"price_gold\",\"price_silver\",\"compare\"]]\n",
    "compare_201603 = pd.merge(future_data_201603,spot_data_201603,left_index=True,right_index=True,suffixes=[\"_future\",\"_spot\"])\n",
    "datatime_201603 = np.array(compare_201603.index.map(str))\n",
    "compare_future_201603 = np.array(compare_201603.loc[:,[\"compare_future\"]])\n",
    "compare_spot_201603 = np.array(compare_201603.loc[:,[\"compare_spot\"]])\n",
    "\n",
    "line_compare_201603 = Line(\"compare_201603\")\n",
    "line_compare_201603.add(\"compare_future\",datatime_201603,compare_future_201603.round(2),mark_point=[\"average\",\"max\",\"min\"],\\\n",
    "                        is_stack=True,is_datazoom_show=True,yaxis_min=30,yaxis_max=90)\n",
    "line_compare_201603.add(\"compare_spot\",datatime_201603,compare_spot_201603,mark_point=[\"average\",\"max\",\"min\"],\\\n",
    "                        is_datazoom_show=True,yaxis_min=30,yaxis_max=90)\n",
    "line_compare_201603"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_future_mins = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\gold_mins.csv\"\n",
    "silver_future_mins = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\silver_mins.csv\"\n",
    "gold_future_mins_data = pd.read_csv(gold_future_mins, index_col=[\"datetime\"])\n",
    "print(\"gold future mins data before duplicated:%d\" % len(gold_future_mins_data))\n",
    "gold_future_mins_data = gold_future_mins_data[~gold_future_mins_data.reset_index().duplicated().values]\n",
    "print(\"gold future mins data after duplicated:%d\" % len(gold_future_mins_data))\n",
    "silver_future_mins_data = pd.read_csv(silver_future_mins, index_col=[\"datetime\"])\n",
    "silver_future_mins_data = silver_future_mins_data[~silver_future_mins_data.reset_index().duplicated().values]\n",
    "\n",
    "future_data_mins = pd.merge(gold_future_mins_data,silver_future_mins_data,left_index=True,right_index=True,suffixes=('_gold','_silver'))\n",
    "#del nan value\n",
    "future_data_mins = future_data_mins.dropna()\n",
    "future_data_mins[\"compare\"] = future_data_mins[\"close_gold\"]/future_data_mins[\"close_silver\"]*1000\n",
    "print(\"future_data_mins:%d\"%len(future_data_mins))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "future_data_mins[\"compare\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_appoint_date(df,strDate,nDays):\n",
    "    '''\n",
    "    获取指定日期往前推或者往后推的几天，输入df是一个包含日期类型的dataframe\n",
    "    df:pandas dataFrame,index需要是str 类型的 date/datetime\n",
    "    strDate:input 日期，如2010-10-10\n",
    "    nDays：往前或者往后的n天,<0 往前推，>0往后推\n",
    "    ''' \n",
    "    #先去重，才能保证前移或者后退的天数准确\n",
    "    if not isinstance(df,pd.DataFrame):\n",
    "        print(\"input df type is not pd.DataFrame\")\n",
    "        return -1\n",
    "    \n",
    "    df = df[~df.reset_index().duplicated().values]\n",
    "    if nDays < 0:\n",
    "        if df.index.tolist().index(strDate) + nDays >= 0:\n",
    "            pre_date = df.index[df.index.tolist().index(strDate) + nDays]\n",
    "            return pre_date\n",
    "        else:\n",
    "            return -1\n",
    "    else:\n",
    "        if df.index.tolist().index(strDate) + nDays <= len(df.index)-1:\n",
    "            after_date = df.index[df.index.tolist().index(strDate) + nDays]\n",
    "            return after_date\n",
    "        else:\n",
    "            return -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_appoint_date(future_data,\"2019/3/9\",-10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp = future_data\n",
    "isinstance(tmp,dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def testAUAGRadio(df,strTradeDate,position):\n",
    "    '''\n",
    "    df:pandas dataFrame,index需要是str 类型的 date/datetime\n",
    "    strTradeDate:str类交易日\n",
    "    position：dict，仓位信息\n",
    "    '''\n",
    "    #if strEnd == None:\n",
    "    #    strEnd = (datetime.today().date()-timedelta(days=1)).strftime('%Y-%m-%d')\n",
    "    if not isinstance(df,pd.DataFrame):\n",
    "        print(\"input df type is not pd.DataFrame\")\n",
    "        return -1\n",
    "    preDate = get_appoint_date(df,strTradeDate,20)\n",
    "    if preDate == -1:\n",
    "        print(\"获取交易日：%s前20天的日期失败\")\n",
    "        return -1\n",
    "    \n",
    "    \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_future = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\gold.csv\"\n",
    "silver_future = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\silver.csv\"\n",
    "\n",
    "stander_dtype = {'open': float, \"close\": float, \"high\": float, \"low\": float, \"volume\":float, \"money\":float  }\n",
    "gold_future_data = pd.read_csv(gold_future, parse_dates=[\"date\"], index_col=[\"date\"], dtype=stander_dtype)\n",
    "gold_future_data = gold_future_data[~gold_future_data.reset_index().duplicated().values]\n",
    "\n",
    "silver_future_data = pd.read_csv(silver_future, parse_dates=[\"date\"], index_col=[\"date\"], dtype=stander_dtype)\n",
    "silver_future_data = silver_future_data[~silver_future_data.reset_index().duplicated().values]\n",
    "\n",
    "future_data = pd.merge(gold_future_data, silver_future_data, left_index=True, right_index=True,\n",
    "                               suffixes=('_gold', '_silver'))\n",
    "future_data[\"compare\"] = future_data[\"close_gold\"] / future_data[\"close_silver\"] * 1000\n",
    "future_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_future_mins = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\gold_mins.csv\"\n",
    "silver_future_mins = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\silver_mins.csv\"\n",
    "gold_future_mins_data = pd.read_csv(gold_future_mins, parse_dates=[\"datetime\"], index_col=[\"datetime\"], dtype=stander_dtype)\n",
    "#print(\"gold future mins data before duplicated:%d\" % len(self.gold_future_mins_data))\n",
    "gold_future_mins_data = gold_future_mins_data[~gold_future_mins_data.reset_index().duplicated().values]\n",
    "#print(\"gold future mins data after duplicated:%d\" % len(self.gold_future_mins_data))\n",
    "silver_future_mins_data = pd.read_csv(silver_future_mins, parse_dates=[\"datetime\"], index_col=[\"datetime\"], dtype=stander_dtype)\n",
    "silver_future_mins_data = silver_future_mins_data[~silver_future_mins_data.reset_index().duplicated().values]\n",
    "\n",
    "future_data_mins = pd.merge(gold_future_mins_data, silver_future_mins_data, left_index=True, right_index=True,\n",
    "                                    suffixes=('_gold', '_silver'))\n",
    "future_data_mins[\"compare\"] = future_data_mins[\"close_gold\"] / future_data_mins[\"close_silver\"] * 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_row', 2000)\n",
    "future_data_mins.loc[\"2016-04-13 14:00:00\":\"2016-04-25 14:00:00\",[\"close_gold\",\"close_silver\",\"compare\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "tmp = future_data_mins.index[-1].date() \n",
    "future_data.loc[tmp]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "future_data_mins.loc[pd.to_datetime(\"2013-03-16 09:01:00\"):pd.to_datetime(\"2013-03-16 23:59:00\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.to_datetime(\"2019-03-13 09:00:00\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_appoint_date_stat_info(future_data, strDate, nDays):\n",
    "    '''\n",
    "    获取指定日期前推或者往后推的几天的统计信息，统计信息来源为合并后的self.future_data\n",
    "    :param strDate:input 日期，如2010-10-10\n",
    "    :param nDays:往前或者往后的n天,<0 往前推，>0往后推\n",
    "    :return:统计信息的pd.dataframe\n",
    "\n",
    "    eg, describe:\n",
    "                       compare\n",
    "             count\t20.000000\n",
    "             mean\t78.479471\n",
    "              std\t0.625898\n",
    "              min\t77.456493\n",
    "             25%\t77.980835\n",
    "             50%\t78.401895\n",
    "             75%\t79.030748\n",
    "             max\t79.437169\n",
    "    '''\n",
    "    inputDate = pd.to_datetime(strDate)\n",
    "    columns_name = [\"count\", \"mean\", \"std\", \"min\", \"25%\", \"50%\", \"75%\", \"max\"]\n",
    "    df_empty = pd.DataFrame(columns=columns_name)\n",
    "    if nDays < 0:\n",
    "        if future_data.index.tolist().index(inputDate) + nDays >= 0:\n",
    "            pre_date = future_data.index[\n",
    "                future_data.index.tolist().index(inputDate) + nDays]\n",
    "            pre_date_1 = future_data.index[\n",
    "                future_data.index.tolist().index(inputDate) - 1]\n",
    "            #print(\"input date: %s, stat begin:%s, end:%s\" % (strDate, pre_date, pre_date_1))\n",
    "            return future_data.loc[pre_date:pre_date_1, [\"compare\"]].describe()\n",
    "        else:\n",
    "            return df_empty\n",
    "    else:\n",
    "        if future_data.index.tolist().index(inputDate) + nDays <= len(\n",
    "                future_data.index) - 1:\n",
    "            after_date = future_data.index[\n",
    "                future_data.index.tolist().index(inputDate) + nDays]\n",
    "            after_date_1 = future_data.index[\n",
    "                future_data.index.tolist().index(inputDate) + 1]\n",
    "            return future_data.loc[after_date_1:after_date, [\"compare\"\n",
    "                                                             ]].describe()\n",
    "        else:\n",
    "            return df_empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_future_data = pd.read_csv(gold_future,parse_dates=[\"date\"], index_col=[\"date\"])\n",
    "gold_future_data = gold_future_data[~gold_future_data.reset_index().duplicated().values]\n",
    "silver_future_data = pd.read_csv(silver_future, parse_dates=[\"date\"],index_col=[\"date\"])\n",
    "silver_future_data = silver_future_data[~silver_future_data.reset_index().duplicated().values]\n",
    "future_data = pd.merge(gold_future_data,silver_future_data,left_index=True,right_index=True,suffixes=('_gold','_silver'))\n",
    "future_data = future_data.dropna()\n",
    "future_data[\"compare\"] = future_data[\"close_gold\"] / future_data[\"close_silver\"] * 1000    \n",
    "future_date_trade_date = future_data.index\n",
    "columns_name = [\"date\",\"count\", \"mean\", \"std\", \"min\", \"25%\", \"50%\", \"75%\", \"max\"]\n",
    "#df_describe = pd.DataFrame(columns=columns_name)\n",
    "#df_describe.index_name = \"date\"\n",
    "data_list = []\n",
    "\n",
    "for trade_date in future_date_trade_date:\n",
    "    tmp_list = []\n",
    "    df_stat = get_appoint_date_stat_info(future_data,trade_date,-20)\n",
    "    if df_stat.empty:\n",
    "        continue\n",
    "    else:\n",
    "        tmp_list = [trade_date, df_stat.loc[\"count\",[\"compare\"]].compare, df_stat.loc[\"mean\",[\"compare\"]].compare, \\\n",
    "                    df_stat.loc[\"std\",[\"compare\"]].compare,df_stat.loc[\"min\",[\"compare\"]].compare, df_stat.loc[\"25%\",[\"compare\"]].compare,\\\n",
    "                   df_stat.loc[\"50%\",[\"compare\"]].compare, df_stat.loc[\"75%\",[\"compare\"]].compare,\\\n",
    "                   df_stat.loc[\"max\",[\"compare\"]].compare]\n",
    "        data_list.append(tmp_list)\n",
    "df_describe = pd.DataFrame(data=data_list,columns=columns_name)\n",
    "df_describe      \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_stat.loc[\"count\",[\"compare\"]].compare"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-03-28\n",
      "2019-03-26\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>compare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>78.922580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.450431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>77.845200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>78.732472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>79.038267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>79.244646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>79.437169</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         compare\n",
       "count  20.000000\n",
       "mean   78.922580\n",
       "std     0.450431\n",
       "min    77.845200\n",
       "25%    78.732472\n",
       "50%    79.038267\n",
       "75%    79.244646\n",
       "max    79.437169"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gold_future = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\gold.csv\"\n",
    "silver_future = \"C:\\\\quanttime\\\\data\\\\gold\\\\sh_future\\\\silver.csv\"\n",
    "\n",
    "stander_dtype = {'open': float, \"close\": float, \"high\": float, \"low\": float, \"volume\": float, \"money\": float}\n",
    "gold_future_data = pd.read_csv(gold_future, parse_dates=[\"date\"], index_col=[\"date\"],\n",
    "                                            dtype=stander_dtype)\n",
    "gold_future_data = gold_future_data[~gold_future_data.reset_index().duplicated().values]\n",
    "\n",
    "silver_future_data = pd.read_csv(silver_future, parse_dates=[\"date\"], index_col=[\"date\"],\n",
    "                                              dtype=stander_dtype)\n",
    "silver_future_data = silver_future_data[~silver_future_data.reset_index().duplicated().values]\n",
    "\n",
    "future_data = pd.merge(gold_future_data, silver_future_data, left_index=True, right_index=True,\n",
    "                                    suffixes=('_gold', '_silver'))\n",
    "future_data[\"compare\"] = future_data[\"close_gold\"] / future_data[\"close_silver\"] * 1000\n",
    "# 去重\n",
    "future_data = future_data.dropna()\n",
    "future_data_trade_date = future_data.index\n",
    "\n",
    "today = datetime.today().date()\n",
    "data_last_date = future_data.index[-1]\n",
    "print(today.strftime(\"%Y-%m-%d\"))\n",
    "print(data_last_date.strftime(\"%Y-%m-%d\"))\n",
    "\n",
    "df_stat_20 = future_data.iloc[-20:]\n",
    "df_stat = df_stat_20.loc[:, [\"compare\"]].describe()\n",
    "df_stat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "79.43716912789078"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stat.loc[\"max\",[\"compare\"]].compare"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
